'''
Created on 2011-10-26

@author: Administrator
'''
import os
import urllib2
import sqlite3
import datetime
import threading
import Queue
import time
import shutil 
defaultfirstdate=20000101
import os
stockdata_rootpath='f:\\stock2011datacenter'
#jobstodopath=os.path.join(stockdata_rootpath,'jobstodo')
#jobsdonepath=os.path.join(stockdata_rootpath,'jobsdone')
rawcontentpath=os.path.join(stockdata_rootpath,'rawcontent')
errordatapath=os.path.join(stockdata_rootpath,'errorlog')
stockfirstdate='2000/1/1'
def str2date(sdate):
    return sdate
    return datetime.date(int(sdate[:4]),int(sdate[4:6]),int(sdate[6:8]))
def int2date(s):
    sdate=str(s)
    if sdate.find('/')<0 and sdate.find('-')<0 :
        return datetime.date(int(sdate[:4]),int(sdate[4:6]),int(sdate[6:8]))
    sdate=sdate.replace('/','-')
    s=[]
    s=sdate.split('-')
    return datetime.date(int(s[0]),int(s[1]),int(s[2]))
def myfloat(src):
    if len(src)==0:return 0.0
    else: return float(src)
class clsStockJobs():
    def __init__(self):       
        
        dbfilename=os.path.join(stockdata_rootpath,str(datetime.date.today())+'_jobs.db3')
        print dbfilename
        if not os.path.exists(dbfilename):
            self.db=sqlite3.connect(dbfilename)
            self.c=self.db.cursor()
            self.createtablestocklist()
            self.updatestocklist()
        else:
            self.db=sqlite3.connect(dbfilename)
            self.c=self.db.cursor()
#            self.updatestocklist()
            
    def getjobslist(self):
        sql='select code,reg,filldate from stocklist order by code'
        self.c.execute(sql)
        ret=self.c.fetchall()
        jobslist={}
        for r in ret:
            jobslist[r[0]+':'+r[1]]=r[2]
        return jobslist
        pass
    def createtablestocklist(self):    
        sql='create table if not exists stocklist(code text,reg text,filldate text,primary key(code))'
        self.c=self.db.cursor()
        self.c.execute(sql)
        self.db.commit()        
    def updatestocklist(self):
        sql='select code from stocklist order by code'
        self.c.execute(sql)
        ret=self.c.fetchall()
        slist=[]  
        for r in ret:
            slist.append(r[0]) 
#        print len(slist),slist
        
        scls=clsGetStockList()
        reglist=['CH','HK','US']
        
        for reg in reglist:
            stocklist=scls.getstocklist(reg)  
                      
            sql='insert into stocklist (code,reg) values(?,?)'
            print reg,len(stocklist)
            for stock in stocklist:
                if stock in slist:pass
                else:self.c.execute(sql,(stock,reg))
            self.db.commit()
    def batchflagupdate(self,codelist):
        for code in codelist:
            sql='update stocklist set filldate=? where code=?'
            self.c.execute(sql,(str(datetime.date.today()),code))
        self.db.commit()            
    def flagonestock(self,code):
        sql='update stocklist set filldate=? where code=?'
        self.c.execute(sql,(str(datetime.date.today()),code))
        self.db.commit()
#    def dojob(self,job):
#        tlist=job.split(':')
#        code=tlist[0]
#        reg=tlist[1]
#        sdb=StockDataDB(code,reg)
#        sdb.updatestockdata()
#        self.flagonestock(code)
class clsStockDataCalc():
    #yearhigh ,yearlow ,ma20 ,ma60 ,ma120 ,radio ,durations
    def __init__(self):
        self.srcdatalist=[]
        self.dstdatalist=[]
        self.closelist=[]
        self.openlist=[]
        self.lowlist=[]
        self.highlist=[]
        self.datenum=0
    def getyearhighandlow(self):
        if self.datenum>54:
            yearhigh=max(self.highlist[self.datenum-54:])
            yearlow=min(self.lowlist[self.datenum-54:])
        else:
            yearhigh=max(self.highlist)
            yearlow=min(self.lowlist)    
        return (yearhigh,yearlow)  
    def getma(self,v):
        mv=0
        if self.datenum<v:return mv
        return round(sum(self.closelist[self.datenum-v:])/v,3)
    def getradio(self):
#        print 'getradio:',self.datenum
        if self.datenum>1:
            pre=self.closelist[self.datenum-2]
#            print 'getradio:',pre,self.closelist[self.datenum-1]
            if pre==0:return 0
            else:
                return round(self.closelist[self.datenum-1]/pre-1,3)
        return 0
    def getdurations(self):
        return 0
    def addoneday(self,onedata):
#        print onedata     
        self.openlist.append(onedata[1])
        self.closelist.append(onedata[2])
        self.highlist.append(onedata[3])
        self.lowlist.append(onedata[4])
        self.datenum=self.datenum+1
        onedayresult=[]   
        for e in range(0,7):
            onedayresult.append(0.0)     
        if self.datenum==0:pass
        else:
            onedayresult[0],onedayresult[1]=self.getyearhighandlow()
            onedayresult[2]=self.getma(20)
            onedayresult[3]=self.getma(60)
            onedayresult[4]=self.getma(120)
            onedayresult[5]=self.getradio()
            onedayresult[6]=self.getdurations()          
           
        return onedayresult
    def calc(self):
        pass
class StockDataDB4Thread():
    def __init__(self,job):
        (self.stockcode,self.reg)=job.split('_')
        self.job=job
#        self.dojobdb()
    def dojobdb(self):
        dbpath=os.path.join(stockdata_rootpath,self.reg)
#        if not os.path.exists(dbpath):os.mkdir(dbpath)
        dbfilename=os.path.join(dbpath,self.stockcode+'.db3')   
        if os.path.exists(dbfilename) :return     
#        print 'dbfilename:',dbfilename
        self.db=sqlite3.connect(dbfilename)
        self.c=self.db.cursor()
        self.createonetable('kday', ' (date integer,open real,close real,high real,low real,yearhigh real,yearlow real,ma20 real,ma60 real,ma120 real,radio real,durations integer,PRIMARY KEY (date))')
        self.updatestockdata()
    def createonetable(self,tablename,para=''):
#        sql='drop table if exists '+tablename
#        self.c.execute(sql)
#        self.db.commit()
        sql='create table if not exists '+tablename+para
        self.c.execute(sql)
        self.db.commit()     
    def calconestockdata(self,datalist):        
        
        dstdatalist={}
        sdc=clsStockDataCalc()
        for srconedata in sorted(datalist.keys()):
            
            oneresult=sdc.addoneday(datalist[srconedata])
#            print datalist[srconedata],oneresult
            dstdatalist[srconedata]=oneresult
        return dstdatalist
        
        pass

    def updatestockdata(self):
        def getonestockdata():
            stockcode=self.stockcode
            stockdata={}
            content=open(os.path.join(rawcontentpath,self.job),'r').read()
            nlist=content.split('\n')
        #            print len(nlist)
            for n in nlist:
                t=n.split('"')
                if len(t)==5:
        #                    print t
                    stockdata[t[0]]=[t[0].strip(),round(myfloat(t[1].strip()),3),round(myfloat(t[2].strip()),3),round(myfloat(t[3].strip()),3),round(myfloat(t[4].strip()),3)]
            return stockdata
        #        print 'dblastdate:',dblastdate,dblastclose
        uprows=0
        #        if dblastdate==datetime.date.today():return uprows                    
        datalist={}
        datalistcalc={}
        datalist=getonestockdata()
        datalistcalc=self.calconestockdata(datalist)
#        print len(datalist)
        if len(datalist)>0: 
            for r in sorted(datalist.keys()):
                
                d=datalist[r]
                dr=datalistcalc[r]
#                print r,d,dr
                sql='insert into kday (date,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio,durations) values(?,?,?,?,?,?,?,?,?,?,?,?)'
                self.c.execute(sql,(r,d[1],d[2],d[3],d[4],dr[0],dr[1],dr[2],dr[3],dr[4],dr[5],dr[6]))
                uprows=uprows+1
            self.db.commit()       
    
class clsGetStockList():
    '''
    get A,HK,US stock list from all vendor
    '''
    def getstocklist(self,reg):
        if reg=='CH':return self.getCNstocklist()
        elif reg=='HK':return self.getHKstocklist()
        elif reg=='US':return self.getUSstocklist()
        else:print 'error reg'
    def getusstocklistfromimeigu(self):
        def getonepage(page):
            url='http://hq.imeigu.com/list.jsp?od=0&ac=0&&pagex='+str(page)
            content=urllib2.urlopen(url).read().decode('utf8')
            s=content.find('<table cellspacing="0">')
            p=content.find('</table',s)
            nlist=content[s:p].split('<tr')
            stocklist={}
            for n in nlist:
    #            print n
                tlist=n.split('<td')
                onestock=[]
                tstock=[]
                for t in tlist:
                    p=t.find('</td')
                    k=t.rfind('</a',0,p)
                    if k>0:p=k
                    s=t.rfind('>',0,p)
                    v=t[s:p].strip(' >\t\n')
                    tstock.append(v)
    #                print '[',v,']',len(v)
                    if len(tstock)>4:
                        onestock.append(tstock[1])
                        onestock.append(tstock[2])
                
            stocklist[onestock[1]]=onestock[2]
            return len(nlist)
        page=1
        ret=getonepage(page)
        print ret
    #    while ret==30:
    #        page=1+page
    #        ret=getonepage(page)         
        return []
    def getHKstocklistfrombloomberg(self):
        def parseonestock(src):
            nlist=src.split('<td')
            ret=[]
            for n in nlist:
                
                p=n.find('</td')
                k=n.rfind('</span',0,p)
                if k>0:p=k
                s=n.rfind('>',0,p)
                v=n[s:p].strip('>\n\t ')
                if len(v)>0: ret.append(v)
            return ret
            pass
        def getonepage(page=1):
            url='http://www.bloomberg.com/markets/companies/country/usa/'+str(page)+'/'
    #        url='http://www.bloomberg.com/markets/companies/country/hong-kong/'+str(page)+'/'
    #        url='http://www.bloomberg.com/markets/companies/country/china/'+str(page)+'/'
            content=urllib2.urlopen(url).read()
            s=content.find('<table class="ticker_data">')
            p=content.find('</table',s)
            nlist=content[s:p].split('<tr')
            stocklist=[]
            for n in nlist:
                ret=parseonestock(n)
                if len(ret)>0:stocklist.append(ret)
            return stocklist
        stocklist=[]
        tlist=[]
        page=1
        tlist=getonepage(page)
        print "page:",page," tlist len:",len(tlist)
        if len(tlist)==0:return 0
        else:
            for t in tlist:
                stocklist.append(t)
            while len(tlist)>0:
                page=page+1
                tlist=getonepage(page)
                print "page:",page," tlist len:",len(tlist)
                for t in tlist:
                    stocklist.append(t)           
        return stocklist
    def getAstocklistfrombloomberg(self):
        def parseonestock(src):
            nlist=src.split('<td')
            ret=[]
            for n in nlist:
                
                p=n.find('</td')
                k=n.rfind('</span',0,p)
                if k>0:p=k
                s=n.rfind('>',0,p)
                v=n[s:p].strip('>\n\t ')
                if len(v)>0: ret.append(v)
            return ret
            pass
        def getonepage(page=1):
            url='http://www.bloomberg.com/markets/companies/country/china/'+str(page)+'/'
            content=urllib2.urlopen(url).read()
            s=content.find('<table class="ticker_data">')
            p=content.find('</table',s)
            nlist=content[s:p].split('<tr')
            stocklist=[]
            for n in nlist:
                ret=parseonestock(n)
                if len(ret)>0:stocklist.append(ret)
            return stocklist
        stocklist=[]
        tlist=[]
        page=1
        tlist=getonepage(page)
        print "page:",page," tlist len:",len(tlist)
        if len(tlist)==0:return 0
        else:
            for t in tlist:
                stocklist.append(t)
         
        return stocklist
    def getHKstocklistfromSINA(self):
        url='http://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/Market_Center.getHKStockData?page=1&num=8000&sort=symbol&asc=1&node=qbgg_hk&_s_r_a=page'
        nlist=urllib2.urlopen(url).read().decode('gb2312').strip('[]{}').split('},{')  
        stocklist={}  
        for n in nlist:
            tlist=n.split(',')
            rlist={}
            for t in tlist:
                plist=t.split(':')
                rlist[plist[0]]=plist[1].strip('"')
            stocklist[rlist['symbol']]=rlist

        return stocklist
    def getUSstockfromSINA(self):
        nodelist=['china_us','yysp_us','meida_us','auto_us','sales_us','finance_us','tech_us']
        urlpre='http://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/Market_Center.getUSList?page=1&num=8000&sort=chg&asc=0&_s_r_a=init&node='
        stocklist={}
        for node in nodelist:
            url=urlpre+node
            nlist=urllib2.urlopen(url).read().strip('{}[]').split('},{')
#            print node,len(nlist)
            for n in nlist:
                tlist=n.split(':')
                code=tlist[1].strip('"')
                stocklist[code]=code
#        print len(stocklist),stocklist
        return stocklist
    def getCHstocklistfromSINA(self):
        url='http://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/Market_Center.getHQNodeData?page=1&num=20000&sort=symbol&asc=1&node=hs_a&_s_r_a=init'
        content=urllib2.urlopen(url).read().decode('gb2312')
#        print content
        clist=content.strip(' []{}').split('},{')
#        print len(clist)
        stocklist={}
        for c in clist:
            flist=c.split(',')
            fsone={}
            for f in flist:
                tlist=f.split(':')
                fsone[tlist[0]]=tlist[1].strip('"')
            stocklist[fsone['code']]=fsone['name']
        return stocklist
    def getHKstocklist(self):
        return self.getHKstocklistfromSINA()
    def getCNstocklist(self):
        return self.getCHstocklistfromSINA()
        pass
    def getUSstocklist(self):
        return self.getUSstockfromSINA()

class ThreadStockDataDB(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)
        self.queue=urlqueue
    def run(self):
        job=self.queue.get()        
        sd=StockDataDB4Thread(job)
        sd.dojobdb()

        self.queue.task_done()
class ThreadStockDataDownload(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)
        self.queue=urlqueue
    def run(self):
        job=self.queue.get()
        
        stockcode=job
        if job.find('HK')>0:stockcode=job.strip('0')
#        print 'thread stockcode:',stockcode
        url='http://www.bloomberg.com/apps/data?pid=webpxta&Securities='+stockcode+'&TimePeriod=5Y&Outfields=HDATE,PR006-H,PR005-H,PR007-H,PR008-H'
        stockcode2=job.replace(':','_')
        try:
            content=urllib2.urlopen(url).read()

        except:
            print '[except]',url
            fname=os.path.join(errordatapath,stockcode2)
            f=open(fname,'w')
            f.close()           
            self.queue.task_done()  
            return 
        if content[:5]=='Error':
            print '[Error]',url
            fname=os.path.join(errordatapath,stockcode2)
            f=open(fname,'w')
            f.write(content)
            f.close()  
            self.queue.task_done()
        else:
            fname=os.path.join(rawcontentpath,stockcode2)
            f=open(fname,'w')
            f.write(content)
            f.close()  
            self.queue.task_done()

           
urlqueue=Queue.Queue()
workers_number=100
class startthread():
    def __init__(self):
        if not os.path.exists(rawcontentpath):os.mkdir(rawcontentpath)
        if not os.path.exists(errordatapath):os.mkdir(errordatapath)
        reglist=['CH','HK','US']     
        for reg in reglist:
            dbpath=os.path.join(stockdata_rootpath,reg)
            if not os.path.exists(dbpath):os.mkdir(dbpath)
        self.todolist=[]
    def dostockdb(self):
#        self.clearpathfile(rawcontentpath)
        jobslist=self.getpathfilelist(rawcontentpath)

        print 'todo job number:',len(jobslist)  
#        for jobs in jobslist:
#            print jobs
#            sdt=StockDataDB4Thread(jobs.replace(':','_'))
#            sdt.dojobdb()
        while len(jobslist)>workers_number:            
            tlist=[]
            tlist=jobslist[:workers_number]
            jobslist=jobslist[workers_number:]
            print len(jobslist)
            self.dostockdbthread(tlist)
    def dostockdbthread(self,todolist):
        jobsnum=len(todolist)
#        print 'jobsnum:',jobsnum
        print 'this round:',jobsnum#,str(datetime.datetime.now()),self.todolist
        for job in todolist[:jobsnum]:
            urlqueue.put(job.replace(':',"_"),block=True ,timeout= 3)              
        for i in range(jobsnum):
            dt=ThreadStockDataDB()
            dt.start()
        urlqueue.join()
        return jobsnum               
    def getpathfilelist(self,mypath):
        flist=[]
        for root,dirs,files in os.walk(mypath):
            for f in files:
                flist.append(f.replace('_',':'))
        return flist        
    def clearpathfile(self,mypath):
        for root,dirs,files in os.walk(mypath):
            for f in files:
                os.remove(os.path.join(root,f))  
    def dojobs(self):
        self.clearpathfile(rawcontentpath)
        jobs=clsStockJobs()
        jobslist=jobs.getjobslist()
        for job in jobslist:
#            print job,jobslist[job]
            
            if jobslist[job]==str(datetime.date.today()):
                pass
            else:
                self.todolist.append(job)  
        print 'todo job number:',len(self.todolist)  
        ret=self.dothread()
        while ret>0:
#            print 'this round todo:',ret
            ret=self.dothread() 
    def dothread(self):        
        self.todolist=self.gettodojobslist()
        jobsnum=len(self.todolist)
        errorlist=self.geterrorjobslist()
        allerror=True
        print 'error list:',errorlist
        for todo in self.todolist:
            if todo in errorlist:pass
            else:allerror=False
        if allerror:return 0
        self.clearpathfile(errordatapath)
#        print 'jobsnum:',jobsnum
        print 'this round:',jobsnum#,str(datetime.datetime.now()),self.todolist
        if jobsnum>workers_number:jobsnum=workers_number
        for job in self.todolist[:jobsnum]:
            urlqueue.put(job,block=True ,timeout= 10)              
        for i in range(jobsnum):
            dt=ThreadStockDataDownload()
            dt.start()
        urlqueue.join()
        return jobsnum       
    def gettodojobslist(self):
        donelist=self.getpathfilelist(rawcontentpath)
#        print donelist
        newtodolist=[]
        for todo in self.todolist:
            if todo in donelist:pass
            else:newtodolist.append(todo)
        return newtodolist
    def geterrorjobslist(self):
        errorlist=self.getpathfilelist(errordatapath)
        return errorlist
    def checkstockfile(self):
        for root,dirs,files in os.walk(rawcontentpath):
            for f in files:
                fname=os.path.join(root,f)
                f=open(fname,'r')
                content=f.read()
                f.close()
                tlist=content.split('\n')
                s=tlist[0].find('Count')
                p=tlist[0].find('=',s)
                q=tlist[0].find('"',p)
                num=int(tlist[0][p+1:q])
                flines=len(tlist)
                if flines==num+3:pass
                else:print len(tlist),num,fname
if __name__ == '__main__':
    print 'this is packStockTakeData  tasklist'
    starttime=datetime.datetime.now()

    sj=startthread()
#    sj.checkstockfile()
    sj.dojobs()
#    sj.dostockdb()
    print 'start and end time:',starttime,datetime.datetime.now()
#    clearallfile()